package hedspi.k55.sm.model;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.DateFormat;
import android.text.format.Time;
import java.text.ParseException;

/**
 * @author hieu
 * @see http://www.vogella.com/articles/AndroidSQLite/article.html
 * 
 */
public class DBmanager extends SQLiteOpenHelper {
	// cac bang trong CSDL
	public static final String TABLE_STOCK = "stock";
	public static final String TABLE_PORTFOLIO = "portfolio";
	public static final String TABLE_TRANSACTION = "_transaction";
	public static final String TABLE_USER = "_user";

	// cac cot trong CSDL
	public static final String _ID = "id";
	public static final String _NAME = "name";
	public static final String _INFO = "info";
	public static final String _CURRENT_MONEY = "currentmoney";
	public static final String _INVESTED_MONEY = "investedmoney";
	public static final String _NUMBER_STOCK = "numberstock";
	public static final String _NOTES = "note";
	public static final String _SYMBOL = "symbol";
	public static final String _PORTFOLIO_ID = "portfolioid";
	public static final String _EXCHANGE_ID = "exchange_id";
	public static final String _PRICE_IN = "pricein";
	public static final String _LAST_PRICE = "lastprice";
	public static final String _SHARES = "shares";
	public static final String _OPEN = "open";
	public static final String _HIGH = "high";
	public static final String _LOW = "low";
	public static final String _MKT_CAP = "maket_capitalization";
	public static final String _VOLUME = "volume";
	public static final String _TYPE = "type_transaction";
	public static final String _STOCK_ID = "stock_id";
	public static final String _DATE = "date";
	public static final String _TIME = "time";
	public static final String _COMMISSION = "commission";
	public static final String _PASSWORD = "password";
	public static final String _EMAIL = "email";

	private static final String DATABASE_NAME = "STOCK_MANAGE_DATABASE";
	private static final int DATABASE_VERSION = 1;
	private static final String PORTFOLIO_NAME = "DEFAULT_PORTFOLIO";
	
	// Database creation sql statement
	private static final String CREATE_PORTFOLIO = "CREATE TABLE "
			+ TABLE_PORTFOLIO + " (" + _ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _NAME
			+ " VARCHAR NOT NULL UNIQUE, " + _CURRENT_MONEY
			+ " DOUBLE DEFAULT(0), " + _INVESTED_MONEY + " DOUBLE DEFAULT(0), "
			+ _NUMBER_STOCK + " INTEGER DEFAULT(0), " + _NOTES + " TEXT "
			+ ");";
	private static final String CREATE_STOCK = "CREATE  TABLE " + "stock"
			+ " (" + " id" + "  INTEGER PRIMARY KEY  NOT NULL  UNIQUE , "
			+ " symbol" + " VARCHAR, " + " companyname" + "  VARCHAR, "
			+ " exchange" + "  VARCHAR, " + " lasttradetime" + "  VARCHAR, "
			+ " change" + "  VARCHAR, " + " perc_change" + "  VARCHAR, "
			+ " openlastprice" + "  VARCHAR, " + " highlowprice" + " VARCHAR, "
			+ " volume" + " VARCHAR, " + " avgvolume" + " VARCHAR, "
			+ " highlow52week" + " VARCHAR, " + " market_cap" + " VARCHAR, "
			+ " pe" + " VARCHAR, " + " beta" + " VARCHAR, " + " eps"
			+ " VARCHAR, " + " shares" + " VARCHAR, " + " dividendyield"
			+ " VARCHAR, " + " avgprice" + " VARCHAR, " + " myshare"
			+ " VARCHAR);";
	private static final String CREATE_TRANSACTION = "CREATE TABLE "
			+ TABLE_TRANSACTION + " (" + _ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _TYPE
			+ " INTEGER NOT NULL, " + _SYMBOL + " VARCHAR NOT NULL, " + _TIME
			+ " DATETIME NOT NULL DEFAULT CURRENT_TIME, " + _SHARES
			+ " INTEGER NOT NULL DEFAULT 0, " + _PRICE_IN
			+ " DOUBLE NOT NULL DEFAULT 0, " + _COMMISSION
			+ " DOUBLE NOT NULL DEFAULT 0, " + _NOTES + " TEXT" + ");";
	private static final String CREATE_USER = "CREATE TABLE " + TABLE_USER
			+ "(" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + _NAME
			+ " VARCHAR NOT NULL, " + _PASSWORD + " VARCHAR NOT NULL, "
			+ _EMAIL + " VARCHAR, " + _CURRENT_MONEY + " DOUBLE DEFAULT(0), "
			+ _INVESTED_MONEY + " DOUBLE DEFAULT(0)" + ");";

	// luu context
	protected Context context;

	public DBmanager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_PORTFOLIO);
		db.execSQL(CREATE_STOCK);
		db.execSQL(CREATE_TRANSACTION);
		db.execSQL(CREATE_USER);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_PORTFOLIO);
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_STOCK);
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_TRANSACTION);
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_USER);
		onCreate(db);
	}

	public boolean isExist(String table, String check, String checkstr) {
		if (table.equalsIgnoreCase(TABLE_TRANSACTION)) {
			return false;
		} else {
			SQLiteDatabase db = this.getReadableDatabase();
			String query = "SELECT * FROM " + table + " WHERE " + check
					+ " like " + "\"" + checkstr + "\"";
			Cursor cursor = null;
			cursor = db.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					cursor.close();
					db.close();
					return true; // ton tai
				}

			}
			cursor.close();
			db.close();
			return false; // ko ton tai
		}
	}

	public Cursor doQuery(String string) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(string, null);
		return cursor;
	}
	/*
	 * public boolean addStock(Stock stock) { SQLiteDatabase db =
	 * this.getWritableDatabase(); ContentValues value = new ContentValues();
	 * 
	 * value.put(_NAME, stock.getCompanyName()); value.put(_SYMBOL,
	 * stock.getSymbol()); value.put(_PORTFOLIO_ID, 1); // viet lai ham
	 * getPortfolioID value.put(_EXCHANGE_ID, 1); // viet lai ham getExchangeID
	 * value.put(_PRICE_IN, stock.getAvgPrice()); // check lai
	 * value.put(_LAST_PRICE, stock.getLastPrice()); value.put(_SHARES,
	 * stock.getShares()); value.put(_OPEN, stock.getOpen()); value.put(_HIGH,
	 * stock.getHigh()); value.put(_LOW, stock.getLow()); value.put(_MKT_CAP,
	 * stock.getMarketCap()); value.put(_VOLUME, stock.getAvgVolume());
	 * 
	 * db.insert(TABLE_STOCK, null, value); db.close(); return true; }
	 * 
	 * public Stock getStock(String symbol) { // truyen them portfolioName
	 * SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor =
	 * db.query(TABLE_STOCK, new String[] { _ID, _NAME, _SYMBOL, _PORTFOLIO_ID,
	 * _EXCHANGE_ID, _PRICE_IN, _LAST_PRICE, _OPEN, _HIGH, _LOW, _MKT_CAP,
	 * _VOLUME }, _SYMBOL + "=?", new String[] { symbol }, null, null, null); if
	 * (cursor != null) { cursor.moveToFirst(); return new
	 * Stock(cursor.getString(1), cursor.getString(2),
	 * Double.parseDouble(cursor.getString(5)),
	 * Double.parseDouble(cursor.getString(6)),
	 * Integer.parseInt(cursor.getString(7)),
	 * Double.parseDouble(cursor.getString(8)),
	 * Double.parseDouble(cursor.getString(9)),
	 * Double.parseDouble(cursor.getString(10)),
	 * Double.parseDouble(cursor.getString(11)),
	 * Double.parseDouble(cursor.getString(12))); } return null; }
	 * 
	 * public Integer getStockID(String symbol) { SQLiteDatabase db =
	 * this.getReadableDatabase(); Cursor cursor = db.query(TABLE_STOCK, new
	 * String[] { _ID, _NAME, _SYMBOL, _PORTFOLIO_ID, _EXCHANGE_ID, _PRICE_IN,
	 * _LAST_PRICE, _OPEN, _HIGH, _LOW, _MKT_CAP, _VOLUME }, _SYMBOL + "=?", new
	 * String[] { symbol }, null, null, null); if (cursor != null) {
	 * cursor.moveToFirst(); return Integer.parseInt(cursor.getString(0)); }
	 * return -1; // -1 if stock symbol isn't exist }
	 * 
	 * public String getStockSymbol(Integer id) { SQLiteDatabase db =
	 * this.getReadableDatabase(); Cursor cursor = db.query(TABLE_STOCK, new
	 * String[] { _ID, _NAME, _SYMBOL, _PORTFOLIO_ID, _EXCHANGE_ID, _PRICE_IN,
	 * _LAST_PRICE, _OPEN, _HIGH, _LOW, _MKT_CAP, _VOLUME }, _ID + "=?", new
	 * String[] { id.toString() }, null, null, null); if (cursor != null) {
	 * cursor.moveToFirst(); return cursor.getString(0); } return null; // null
	 * if stock id isn't exist }
	 * 
	 * public boolean removeStock(Stock stock) { SQLiteDatabase db =
	 * this.getWritableDatabase(); db.delete(TABLE_STOCK, _SYMBOL + "=?", new
	 * String[] { stock.getSymbol() }); db.close(); return true; }
	 * 
	 * public List<Stock> findStock(String symbol) { List<Stock> list = new
	 * ArrayList<Stock>(); SQLiteDatabase db = this.getReadableDatabase();
	 * 
	 * String query = "SELECT * FROM" + TABLE_STOCK + "WHERE " + _SYMBOL + "=" +
	 * symbol; Cursor cursor = db.rawQuery(query, null); if (cursor != null) {
	 * cursor.moveToFirst(); do { Stock stock = new Stock(cursor.getString(1),
	 * cursor.getString(2), Double.parseDouble(cursor .getString(5)),
	 * Double.parseDouble(cursor .getString(6)), Integer.parseInt(cursor
	 * .getString(7)), Double.parseDouble(cursor .getString(8)),
	 * Double.parseDouble(cursor .getString(9)), Double.parseDouble(cursor
	 * .getString(10)), Double.parseDouble(cursor .getString(11)),
	 * Double.parseDouble(cursor .getString(12))); list.add(stock); } while
	 * (cursor.moveToNext()); db.close(); return list; } db.close(); return
	 * null; }
	 * 
	 * public List<Stock> getAllStock(String PortfolioName) { List<Stock> list =
	 * new ArrayList<Stock>(); SQLiteDatabase db = this.getReadableDatabase();
	 * 
	 * String query = "SELECT * FROM" + TABLE_STOCK + "WHERE" + _PORTFOLIO_ID +
	 * "=" + getIdPortfolio(PortfolioName); Cursor cursor = db.rawQuery(query,
	 * null); if (cursor != null) { cursor.moveToFirst(); do { Stock stock = new
	 * Stock(cursor.getString(1), cursor.getString(2), Double.parseDouble(cursor
	 * .getString(5)), Double.parseDouble(cursor .getString(6)),
	 * Integer.parseInt(cursor .getString(7)), Double.parseDouble(cursor
	 * .getString(8)), Double.parseDouble(cursor .getString(9)),
	 * Double.parseDouble(cursor .getString(10)), Double.parseDouble(cursor
	 * .getString(11)), Double.parseDouble(cursor .getString(12)));
	 * list.add(stock); } while (cursor.moveToNext()); db.close(); return list;
	 * } db.close(); return null; }
	 * 
	 * public boolean addPortfolio(Portfolio portfolio) { SQLiteDatabase db =
	 * this.getWritableDatabase(); ContentValues value = new ContentValues();
	 * 
	 * value.put(_NAME, portfolio.getName()); value.put(_CURRENT_MONEY,
	 * portfolio.getCurrentMoney()); value.put(_INVESTED_MONEY,
	 * portfolio.getInvestedMoney()); value.put(_NUMBER_STOCK,
	 * portfolio.getNumberStock()); value.put(_NOTES, portfolio.getNotes());
	 * 
	 * db.insert(TABLE_PORTFOLIO, null, value); db.close(); return true; }
	 * 
	 * public int getIdPortfolio(String PortfolioName) { SQLiteDatabase db =
	 * this.getReadableDatabase(); Cursor cursor = db.query(TABLE_PORTFOLIO, new
	 * String[] { _ID, _NAME, _CURRENT_MONEY, _INVESTED_MONEY, _NUMBER_STOCK,
	 * _NOTES }, _NAME + "=?", new String[] { PortfolioName }, null, null,
	 * null); if (cursor != null) { cursor.moveToFirst(); return
	 * Integer.parseInt(cursor.getString(0)); } return -1; // -1 if portfolio
	 * name isn't exist }
	 * 
	 * public Portfolio getPortfolio(String PortfolioName) { SQLiteDatabase db =
	 * this.getReadableDatabase(); Cursor cursor = db.query(TABLE_PORTFOLIO, new
	 * String[] { _ID, _NAME, _CURRENT_MONEY, _INVESTED_MONEY, _NUMBER_STOCK,
	 * _NOTES }, _NAME + "=?", new String[] { PortfolioName }, null, null,
	 * null);
	 * 
	 * if (cursor != null) { cursor.moveToFirst(); return new
	 * Portfolio(Integer.parseInt(cursor.getString(0)), cursor.getString(1),
	 * Double.parseDouble(cursor.getString(2)),
	 * Double.parseDouble(cursor.getString(3)),
	 * Integer.parseInt(cursor.getString(4)), cursor.getString(5)); } return
	 * null; }
	 * 
	 * public List<Portfolio> getAllPortolio() { List<Portfolio> list = new
	 * ArrayList<Portfolio>(); SQLiteDatabase db = this.getReadableDatabase();
	 * 
	 * String query = "SELECT * FROM" + TABLE_PORTFOLIO; Cursor cursor =
	 * db.rawQuery(query, null); if (cursor != null) { cursor.moveToFirst(); do
	 * { Portfolio portfolio = new Portfolio(Integer.parseInt(cursor
	 * .getString(0)), cursor.getString(1),
	 * Double.parseDouble(cursor.getString(2)),
	 * Double.parseDouble(cursor.getString(3)),
	 * Integer.parseInt(cursor.getString(4)), cursor.getString(5));
	 * list.add(portfolio); } while (cursor.moveToNext()); db.close(); return
	 * list; } db.close(); return null; }
	 * 
	 * public boolean removePortfolio(String portfolioName) { SQLiteDatabase db
	 * = this.getWritableDatabase(); db.delete(TABLE_PORTFOLIO, _NAME + "=?",
	 * new String[] { portfolioName }); db.close(); return true; }
	 * 
	 * // zend update(15/4) public boolean addTransaction(Transaction
	 * transaction) { SQLiteDatabase db = this.getWritableDatabase();
	 * ContentValues value = new ContentValues();
	 * 
	 * value.put(_TYPE, transaction.getType()); value.put(_STOCK_ID,
	 * transaction.getStockId()); value.put(_TIME,
	 * transaction.getTime().toString()); // value.put(_DATE,
	 * transaction.getTime().toString()); value.put(_SHARES,
	 * transaction.getShares()); value.put(_PRICE_IN, transaction.getPrice());
	 * value.put(_COMMISSION, transaction.getCommission()); value.put(_NOTES,
	 * transaction.getNotes());
	 * 
	 * db.insert(TABLE_TRANSACTION, null, value); db.close(); return true; }
	 * 
	 * // get all transaction - use for history transaction public
	 * List<Transaction> getAllTransaction() throws NumberFormatException,
	 * ParseException { List<Transaction> list = new ArrayList<Transaction>();
	 * SQLiteDatabase db = this.getReadableDatabase();
	 * 
	 * String query = "SELECT * FROM" + TABLE_TRANSACTION; Cursor cursor =
	 * db.rawQuery(query, null); if (cursor != null) { cursor.moveToFirst(); do
	 * { Transaction transaction = new Transaction(
	 * Integer.parseInt(cursor.getString(1)),
	 * Integer.parseInt(cursor.getString(2)), new Time( cursor.getString(3)),
	 * Integer.parseInt(cursor .getString(4)), Double.parseDouble(cursor
	 * .getString(5)), Double.parseDouble(cursor .getString(6)),
	 * cursor.getString(7)); list.add(transaction); } while
	 * (cursor.moveToNext()); db.close(); return list; } db.close(); return
	 * null;
	 * 
	 * }
	 * 
	 * // get all transaction create by 1 stock public List<Transaction>
	 * getTransaction(String symbol) throws NumberFormatException,
	 * ParseException { List<Transaction> list = new ArrayList<Transaction>();
	 * SQLiteDatabase db = this.getReadableDatabase();
	 * 
	 * Cursor cursor = db.query(TABLE_TRANSACTION, new String[] { _ID, _TYPE,
	 * _STOCK_ID, _TIME, _DATE, _SHARES, _PRICE_IN, _COMMISSION, _NOTES },
	 * _STOCK_ID + "=?", new String[] { getStockID(symbol) .toString() }, null,
	 * null, null); if (cursor != null) { cursor.moveToFirst(); do { Transaction
	 * transaction = new Transaction( Integer.parseInt(cursor.getString(1)),
	 * Integer.parseInt(cursor.getString(2)), new Time( cursor.getString(3)),
	 * Integer.parseInt(cursor .getString(4)), Double.parseDouble(cursor
	 * .getString(5)), Double.parseDouble(cursor .getString(6)),
	 * cursor.getString(7)); list.add(transaction); } while
	 * (cursor.moveToNext()); db.close(); return list; } db.close(); return
	 * null; }
	 * 
	 * public boolean updateTransaction(Transaction transaction, Integer id) {
	 * SQLiteDatabase db = this.getWritableDatabase(); ContentValues value = new
	 * ContentValues();
	 * 
	 * value.put(_TYPE, transaction.getType()); value.put(_STOCK_ID,
	 * transaction.getStockId()); value.put(_TIME,
	 * transaction.getTime().toString()); value.put(_SHARES,
	 * transaction.getShares()); value.put(_PRICE_IN, transaction.getPrice());
	 * value.put(_COMMISSION, transaction.getCommission()); value.put(_NOTES,
	 * transaction.getNotes()); db.update(TABLE_TRANSACTION, value, _ID, new
	 * String[] { id.toString() });
	 * 
	 * return true; }
	 */
}
